package com.sdkj.util;

import java.io.IOException;
import java.text.DecimalFormat;
import java.util.LinkedList;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;


public class ReadExcel {
	
	private static XSSFWorkbook xwb;



	/**
     * 对外提供读取excel 的方法
     * */
    public static List<List<Object>> readExcel(MultipartFile file,String type) throws IOException {
        String fileName = file.getOriginalFilename();
        String extension = fileName.lastIndexOf(".") == -1 ? "" : fileName.substring(fileName.lastIndexOf(".") + 1);
        List<List<Object>> list = new LinkedList<List<Object>>();
        if ("xls".equals(extension)) {
        	if ("0".equals(type)) {
        		 list=read2003ExcelForJS(file);//建设银行
			}else if ("1".equals(type)) {
	             list=read2003ExcelForXA(file);//西安银行
	        }else if ("2".equals(type)) {
	        	list= read2003ExcelForZS(file);//招商银行
	        }else if ("3".equals(type)) {
	        	list= read2003ExcelForZX(file);//中信银行
	        }
        	 return list;
        } else if ("xlsx".equals(extension)) {
        	if ("0".equals(type)) {
        		list=read2007ExcelJS(file);//建设银行
			}else if ("1".equals(type)) {
				list=  read2007ExcelForXA(file);//西安银行
	        }else if ("2".equals(type)) {
	        	list=  read2007ExcelForZS(file);//招商银行
	        }else if ("3".equals(type)) {
	        	list=  read2007ExcelForZX(file);//中信银行
	        }
        	   return list;
        }else {
            throw new IOException("不支持的文件类型");
        }
    }
    
    
    
    
    

	 /**
    * 读取 office 2003 excel
    * 
    * @throws IOException
    * @throws FileNotFoundException
    */
   public static List<List<Object>> read2003ExcelForJS(MultipartFile file)throws IOException {
       List<List<Object>> list = new LinkedList<List<Object>>();
       HSSFWorkbook hwb = new HSSFWorkbook(file.getInputStream());
       HSSFSheet sheet = hwb.getSheetAt(0);
       Object value = null;
       HSSFRow row = null;
       HSSFCell cell = null;
       for (int i = 1; i < sheet.getPhysicalNumberOfRows(); i++) {
           row = sheet.getRow(i);
           if (row == null) {
               continue;
           }
           List<Object> linked = new LinkedList<Object>();
           for (int j = 0; j <= 13; j++) {
               cell = row.getCell(j);
               if (cell == null) {
                   continue;
               }
               DecimalFormat nf = new DecimalFormat("0.00");// 格式化数字
               switch (cell.getCellType()) {
               case XSSFCell.CELL_TYPE_STRING:
                   value = cell.getStringCellValue();
                   break;
               case XSSFCell.CELL_TYPE_NUMERIC:
                       value = nf.format(cell.getNumericCellValue());
                   break;
               case XSSFCell.CELL_TYPE_BLANK:
                       value = "";
                       break;
               }
               linked.add(value);
           }
           list.add(linked);
       }
       return list;
   }
   
   
   /**
    * 读取 office 2003 excel
    * 
    * @throws IOException
    * @throws FileNotFoundException
    */
public static List<List<Object>> read2003ExcelForXA(MultipartFile file)throws IOException {
       List<List<Object>> list = new LinkedList<List<Object>>();
       HSSFWorkbook hwb = new HSSFWorkbook(file.getInputStream());
       HSSFSheet sheet = hwb.getSheetAt(0);
       Object value = null;
       HSSFRow row = null;
       HSSFCell cell = null;
       for (int i = 1; i < sheet.getPhysicalNumberOfRows(); i++) {
           row = sheet.getRow(i);
           if (row == null) {
               continue;
           }
           List<Object> linked = new LinkedList<Object>();
           for (int j = row.getFirstCellNum(); j <= 8; j++) {
               cell = row.getCell(j);
               DecimalFormat nf = new DecimalFormat("0.00");// 格式化数字
               switch (cell.getCellType()) {
               case XSSFCell.CELL_TYPE_STRING:
                   value = cell.getStringCellValue();
                   break;
               case XSSFCell.CELL_TYPE_NUMERIC:
                       value = nf.format(cell.getNumericCellValue());
                   break;
               case XSSFCell.CELL_TYPE_BLANK:
                       value = "";
                       break;
               }
               linked.add(value);
           }
           list.add(linked);
       }
       return list;
   }
   
   
   
   /**
    * 读取 office 2003 excel
    * 
    * @throws IOException
    * @throws FileNotFoundException
    */
   public static List<List<Object>> read2003ExcelForZS(MultipartFile file)throws IOException {
       List<List<Object>> list = new LinkedList<List<Object>>();
       HSSFWorkbook hwb = new HSSFWorkbook(file.getInputStream());
       HSSFSheet sheet = hwb.getSheetAt(0);
       Object value = null;
       HSSFRow row = null;
       HSSFCell cell = null;
       for (int i = 4; i < sheet.getPhysicalNumberOfRows(); i++) {
		   row = sheet.getRow(i);
		   if (row == null) {
			   continue;
		   }
		   List<Object> linked = new LinkedList<Object>();
		   if (i<10) {
			   for (int j = row.getFirstCellNum(); j < 6; j++) {
				   cell = row.getCell(j);
				   if (cell==null) {
					   continue;
				   }
				   DecimalFormat nf = new DecimalFormat("0.00");// 格式化数字
				   switch (cell.getCellType()) {
				   case XSSFCell.CELL_TYPE_STRING:
					   value = cell.getStringCellValue();
					   break;
				   case XSSFCell.CELL_TYPE_NUMERIC:
					   value = nf.format(cell.getNumericCellValue());
					   break;
				   case XSSFCell.CELL_TYPE_BLANK:
					   value = "";
					   break;
				   }
				   linked.add(value);
			   }
		}else if(i>=13){
			 for (int j = row.getFirstCellNum(); j <= 20; j++) {
				   cell = row.getCell(j);
				   DecimalFormat nf = new DecimalFormat("0.00");// 格式化数字
				   switch (cell.getCellType()) {
				   case XSSFCell.CELL_TYPE_STRING:
					   value = cell.getStringCellValue();
					   break;
				   case XSSFCell.CELL_TYPE_NUMERIC:
					   value = nf.format(cell.getNumericCellValue());
					   break;
				   case XSSFCell.CELL_TYPE_BLANK:
					   value = "";
					   break;
				   }
				   linked.add(value);
			   }
		}
		   list.add(linked);
	   }
       return list;
   }
   
   
   
   /**
    * 读取 office 2003 excel
    * 
    * @throws IOException
    * @throws FileNotFoundException
    */
   public static List<List<Object>> read2003ExcelForZX(MultipartFile file)throws IOException {
       List<List<Object>> list = new LinkedList<List<Object>>();
       HSSFWorkbook hwb = new HSSFWorkbook(file.getInputStream());
       HSSFSheet sheet = hwb.getSheetAt(0);
       Object value = null;
       HSSFRow row = null;
       HSSFCell cell = null;
       for (int i = 7; i < sheet.getPhysicalNumberOfRows(); i++) {
           row = sheet.getRow(i);
           if (row == null) {
               continue;
           }
           //中信测试
           List<Object> linked = new LinkedList<Object>();
           if (i<=7) {
        	   for (int j = row.getFirstCellNum(); j < 3; j++) {
        		   cell = row.getCell(j);
        		   DecimalFormat nf = new DecimalFormat("0.00");// 格式化数字
        		   switch (cell.getCellType()) {
        		   case XSSFCell.CELL_TYPE_STRING:
        			   value = cell.getStringCellValue();
        			   break;
        		   case XSSFCell.CELL_TYPE_NUMERIC:
        			   value = nf.format(cell.getNumericCellValue());
        			   break;
        		   case XSSFCell.CELL_TYPE_BLANK:
        			   value = "";
        			   break;
        		   }
        		   linked.add(value);
        	   }
           }else if(i>=10){
        	   for (int j = row.getFirstCellNum(); j < row.getLastCellNum(); j++) {
        		   cell = row.getCell(j);
        		   DecimalFormat nf = new DecimalFormat("0.00");// 格式化数字
        		   switch (cell.getCellType()) {
        		   case XSSFCell.CELL_TYPE_STRING:
        			   value = cell.getStringCellValue();
        			   break;
        		   case XSSFCell.CELL_TYPE_NUMERIC:
        			   value = nf.format(cell.getNumericCellValue());
        			   break;
        		   case XSSFCell.CELL_TYPE_BLANK:
        			   value = "";
        			   break;
        		   }
        		   linked.add(value);
        	   }
           }
           list.add(linked);
       }
       return list;
   }
   
   /**********************************************************2007EXCEL解析********************************************************/
   
   
   
   /**
    * 读取Office 2007 excel
    * */
   public static List<List<Object>> read2007ExcelJS(MultipartFile file) throws IOException {
       List<List<Object>> list = new LinkedList<List<Object>>();
       // 构造 XSSFWorkbook 对象，strPath 传入文件路径
       XSSFWorkbook xwb = new XSSFWorkbook(file.getInputStream());
       // 读取第一章表格内容
       XSSFSheet sheet = xwb.getSheetAt(0);
       Object value = null;
       XSSFRow row = null;
       XSSFCell cell = null;
       for (int i = 1; i < sheet.getPhysicalNumberOfRows(); i++) {
           row = sheet.getRow(i);
           if (row == null) {
               continue;
           } 
           List<Object> linked = new LinkedList<Object>();
           for (int j = 0; j <= 13; j++) {
               cell = row.getCell(j);
               if (cell == null) {
                   continue;
               }
               DecimalFormat nf = new DecimalFormat("0.00");// 格式化数字
               switch (cell.getCellType()) {
               case XSSFCell.CELL_TYPE_STRING:
                   value = cell.getStringCellValue();
                   break;
               case XSSFCell.CELL_TYPE_NUMERIC:
                       value = nf.format(cell.getNumericCellValue());
                   break;
               case XSSFCell.CELL_TYPE_BLANK:
                       value = "";
                       break;
               }
               linked.add(value);
           }
           list.add(linked);
       }
       return list;
   }
   
   
   
   
   /**
    * 读取Office 2007 excel
    * */
   public static List<List<Object>> read2007ExcelForXA(MultipartFile file) throws IOException {
	   List<List<Object>> list = new LinkedList<List<Object>>();
	   // 构造 XSSFWorkbook 对象，strPath 传入文件路径
	   XSSFWorkbook xwb = new XSSFWorkbook(file.getInputStream());
	   // 读取第一章表格内容
	   XSSFSheet sheet = xwb.getSheetAt(0);
	   Object value = null;
	   XSSFRow row = null;
	   XSSFCell cell = null;
	   for (int i = 1; i < sheet.getPhysicalNumberOfRows(); i++) {
		   row = sheet.getRow(i);
		   if (row == null) {
			   continue;
		   }
		   List<Object> linked = new LinkedList<Object>();
		   for (int j = row.getFirstCellNum(); j <= 8; j++) {
			   cell = row.getCell(j);
			   DecimalFormat nf = new DecimalFormat("0.00");// 格式化数字
			   switch (cell.getCellType()) {
               case XSSFCell.CELL_TYPE_STRING:
                   value = cell.getStringCellValue();
                   break;
               case XSSFCell.CELL_TYPE_NUMERIC:
                       value = nf.format(cell.getNumericCellValue());
                   break;
               case XSSFCell.CELL_TYPE_BLANK:
                       value = "";
                       break;
               }
			   linked.add(value);
		   }
		   list.add(linked);
	   }
	   return list;
   }
   
   
   
   /**
    * 读取Office 2007 excel
    * */
   public static List<List<Object>> read2007ExcelForZS(MultipartFile file) throws IOException {
	   List<List<Object>> list = new LinkedList<List<Object>>();
	   xwb = new XSSFWorkbook(file.getInputStream());
	   // 读取第一章表格内容
	   XSSFSheet sheet = xwb.getSheetAt(0);
	   Object value = null;
	   XSSFRow row = null;
	   XSSFCell cell = null;
	   for (int i = 4; i < sheet.getPhysicalNumberOfRows(); i++) {
		   row = sheet.getRow(i);
		   if (row == null) {
			   continue;
		   }
		   List<Object> linked = new LinkedList<Object>();
		   if (i<10) {
			   for (int j = row.getFirstCellNum(); j < 6; j++) {
				   cell = row.getCell(j);
				   if (cell==null) {
					   continue;
				   }
				   DecimalFormat nf = new DecimalFormat("0.00");// 格式化数字
				   switch (cell.getCellType()) {
				   case XSSFCell.CELL_TYPE_STRING:
					   value = cell.getStringCellValue();
					   break;
				   case XSSFCell.CELL_TYPE_NUMERIC:
					   value = nf.format(cell.getNumericCellValue());
					   break;
				   case XSSFCell.CELL_TYPE_BLANK:
					   value = "";
					   break;
				   }
				   linked.add(value);
			   }
		}else if(i>=13){
			 for (int j = row.getFirstCellNum(); j <= 20; j++) {
				   cell = row.getCell(j);
				   DecimalFormat nf = new DecimalFormat("0.00");// 格式化数字
				   switch (cell.getCellType()) {
				   case XSSFCell.CELL_TYPE_STRING:
					   value = cell.getStringCellValue();
					   break;
				   case XSSFCell.CELL_TYPE_NUMERIC:
					   value = nf.format(cell.getNumericCellValue());
					   break;
				   case XSSFCell.CELL_TYPE_BLANK:
					   value = "";
					   break;
				   }
				   linked.add(value);
			   }
		}
		   list.add(linked);
	   }
	   
	   return list;
   }
   
   
   
   /**
    * 读取Office 2007 excel (中信)
    * */
   public static List<List<Object>> read2007ExcelForZX(MultipartFile file) throws IOException {
       List<List<Object>> list = new LinkedList<List<Object>>();
       // 构造 XSSFWorkbook 对象，strPath 传入文件路径
       XSSFWorkbook xwb = new XSSFWorkbook(file.getInputStream());
       // 读取第一章表格内容
       XSSFSheet sheet = xwb.getSheetAt(0);
       Object value = null;
       XSSFRow row = null;
       XSSFCell cell = null;
       for (int i = 7; i < sheet.getPhysicalNumberOfRows(); i++) {
           row = sheet.getRow(i);
           if (row == null) {
               continue;
           }
           //中信测试
           List<Object> linked = new LinkedList<Object>();
           if (i<=7) {
        	   for (int j = row.getFirstCellNum(); j < 3; j++) {
        		   cell = row.getCell(j);
        		   DecimalFormat nf = new DecimalFormat("0.00");// 格式化数字
        		   switch (cell.getCellType()) {
        		   case XSSFCell.CELL_TYPE_STRING:
        			   value = cell.getStringCellValue();
        			   break;
        		   case XSSFCell.CELL_TYPE_NUMERIC:
        			   value = nf.format(cell.getNumericCellValue());
        			   break;
        		   case XSSFCell.CELL_TYPE_BLANK:
        			   value = "";
        			   break;
        		   }
        		   linked.add(value);
        	   }
           }else if(i>=10){
        	   for (int j = row.getFirstCellNum(); j < row.getLastCellNum(); j++) {
        		   cell = row.getCell(j);
        		   DecimalFormat nf = new DecimalFormat("0.00");// 格式化数字
        		   switch (cell.getCellType()) {
        		   case XSSFCell.CELL_TYPE_STRING:
        			   value = cell.getStringCellValue();
        			   break;
        		   case XSSFCell.CELL_TYPE_NUMERIC:
        			   value = nf.format(cell.getNumericCellValue());
        			   break;
        		   case XSSFCell.CELL_TYPE_BLANK:
        			   value = "";
        			   break;
        		   }
        		   linked.add(value);
        	   }
           }
           list.add(linked);
       }
       return list;
   }
	
}
